﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using FindIt.BLL;
using MySql.Data.MySqlClient;
using System.Configuration;

namespace FindIt.DAL
{
    public class UserDB
    {
        private MySqlConnection conn;
        private MySqlCommand cmd;

        public UserDB()
        {
            conn = new MySqlConnection(ConfigurationManager.AppSettings["connectionString"]);
            cmd = new MySqlCommand();
            cmd.Connection = conn;
        }

        public void AddUser(User user)
        {
            cmd.CommandText = "INSERT INTO tblUser (FirstName, LastName, Username, Password, Email, Role) "
                                        + "VALUES(@firstname, @lastName, @username2, @password, @email, @role)";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@firstName", MySqlDbType.VarChar).Value = user.FirstName;
            cmd.Parameters.Add("@lastName", MySqlDbType.VarChar).Value = user.LastName;
            cmd.Parameters.Add("@username2", MySqlDbType.VarChar).Value = user.Username;
            cmd.Parameters.Add("@password", MySqlDbType.VarChar).Value = user.Password;
            cmd.Parameters.Add("@email", MySqlDbType.VarChar).Value = user.Email;
            cmd.Parameters.Add("@role", MySqlDbType.Enum).Value = user.Role;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }

            catch (MySqlException ex)
            {
                string msg = "Insert Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }
        }
             
        public void AddWU(WaitingUser wu)
        {
            cmd.CommandText = "INSERT INTO tblWaitingUser (Username, Status) "
                                        + "VALUES(@username, @status)";

            cmd.Parameters.Clear();
            cmd.Parameters.Add("@username", MySqlDbType.VarChar).Value = wu.Username;
            cmd.Parameters.Add("@status", MySqlDbType.Enum).Value = wu.Password;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }

            catch (MySqlException ex)
            {
                string msg = "Insert Error:";
                msg += ex.Message;
                throw new Exception(msg);
                              
            }

            finally
            {
                conn.Close();
            }
        }


        public MySqlDataReader GetUserByUsername(string username)
        {
            cmd.CommandText = "SELECT * FROM tblUser WHERE Username = @username";

            cmd.Parameters.Clear();
            cmd.Parameters.Add("@username", MySqlDbType.VarChar).Value = username;

            MySqlDataReader dr;

            try
            {
                conn.Open();
                dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }

            catch (MySqlException ex)
            {
                string msg = "Insert Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            return dr;
        }

        public MySqlDataReader GetPUByUsername(string username)
        {
            cmd.CommandText = "SELECT * FROM tblPrivilegedUser WHERE Username = @username";

            cmd.Parameters.Clear();
            cmd.Parameters.Add("@username", MySqlDbType.VarChar).Value = username;

            MySqlDataReader dr;

            try
            {
                conn.Open();
                dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
            }

            catch (MySqlException ex)
            {
                string msg = "Insert Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            return dr;
        }

        public User.RoleType GetRoleByUsername(string username)
        {
            cmd.CommandText = "SELECT Role FROM tblUser WHERE Username = @username";

            cmd.Parameters.Clear();
            cmd.Parameters.Add("@username", MySqlDbType.VarChar).Value = username;

            User.RoleType roleType;

            try
            {
                conn.Open();
                roleType = (User.RoleType) Enum.Parse(typeof(User.RoleType), cmd.ExecuteScalar().ToString());
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }

            return roleType;
        }

        public void ChangeCredit(string username, double difference)
        {
            cmd.CommandText = "UPDATE tblPrivilegedUSer SET Credit = (Credit + @difference) WHERE username = @username;";

            cmd.Parameters.Clear();
            cmd.Parameters.Add("@username", MySqlDbType.VarChar).Value = username;
            cmd.Parameters.Add("@difference", MySqlDbType.Decimal).Value = difference;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }
        }

        public void WarnPU(string username)
        {
            cmd.CommandText = "UPDATE tblPrivilegedUSer SET Status = @status WHERE username = @username;";

            cmd.Parameters.Clear();
            cmd.Parameters.Add("@username", MySqlDbType.VarChar).Value = username;
            cmd.Parameters.Add("@status", MySqlDbType.Enum).Value = PrivilegedUser.StatusType.warned;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }
        }

        public void RemovePU(string username)
        {
            cmd.CommandText = "UPDATE tblPrivilegedUSer SET Status = @status WHERE username = @username;";

            cmd.Parameters.Clear();
            cmd.Parameters.Add("@username", MySqlDbType.VarChar).Value = username;
            cmd.Parameters.Add("@status", MySqlDbType.Enum).Value = PrivilegedUser.StatusType.removed;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }
        }

<<<<<<< .mine
=======

>>>>>>> .r49

        public bool CheckIfUserNameExists(string username)
        {
            cmd.CommandText = "SELECT IF(EXISTS (SELECT Username FROM tblUser WHERE Username = @username), true ,false) FROM DUAL";

<<<<<<< .mine
=======


 

>>>>>>> .r49
            cmd.Parameters.Add("@username", MySqlDbType.VarChar).Value = username;

            bool result;

            try
            {
                conn.Open();
                string resultString = cmd.ExecuteScalar().ToString();
                if (int.Parse(resultString) == 1)
                    result = true;
                else
                    result = false;
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }

            return result;
        }

<<<<<<< .mine
        public bool LoginUser(string username, string password)
        {
            cmd.CommandText = "SELECT IF(EXISTS (SELECT Username FROM tblUser WHERE Username = @username AND Password = @password), true ,false) FROM DUAL";
=======
>>>>>>> .r49

<<<<<<< .mine
            cmd.Parameters.Clear();
            cmd.Parameters.Add("@username", MySqlDbType.VarChar).Value = username;
            cmd.Parameters.Add("@password", MySqlDbType.VarChar).Value = password;

            bool result;

            try
            {
                conn.Open();
                string resultString = cmd.ExecuteScalar().ToString();
                if (int.Parse(resultString) == 1)
                    result = true;
                else
                    result = false;
            }

            catch (MySqlException ex)
            {
                string msg = "Query Error:";
                msg += ex.Message;
                throw new Exception(msg);
            }

            finally
            {
                conn.Close();
            }

            return result;
        }
=======
>>>>>>> .r49
    }
}